package com.mvity.framework.common.dao.impl;

import java.io.Serializable;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;
import javax.persistence.Column;

import org.apache.commons.lang3.ArrayUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.stereotype.Repository;

import com.mvity.framework.common.base.BaseEntity;
import com.mvity.framework.common.dao.BaseJdbcDao;

/**
 * JDBC DAO 基类
 * 
 * @author vity
 * @date 2012-3-15
 * @mail vity.1215@gmail.com
 */
@Repository(value = "baseJdbcDao")
public class BaseJdbcDaoImpl implements Serializable, BaseJdbcDao {

	private static final long serialVersionUID = 3263370978521101147L;

	protected static final Logger logger = LoggerFactory.getLogger(BaseJdbcDaoImpl.class);

	@Resource
	protected JdbcTemplate jdbcTemplate;

	/**
	 * 数据库类型
	 */
	@Value("${jdbc.driverClassName}")
	protected String driverClass;

	/**
	 * 获取 Connection
	 * 
	 * @return
	 * @throws SQLException
	 */
	protected Connection getConnection() throws SQLException {
		return DataSourceUtils.getConnection(jdbcTemplate.getDataSource());
	}

	/**
	 * 释放连接
	 * 
	 * @param con
	 */
	protected void releaseConnection(Connection conn) {
		if (conn != null)
			DataSourceUtils.releaseConnection(conn, jdbcTemplate.getDataSource());
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.mvity.framework.common.dao.impl.BaseJdbcDao#executeQuerySQL(java.
	 * lang.Class, java.lang.String)
	 */
	@Override
	public <T extends BaseEntity> List<T> executeQuerySQL(Class<T> entityClass, String sql) throws SQLException {
		return this.executeQuerySQL(entityClass, sql, null);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.mvity.framework.common.dao.impl.BaseJdbcDao#executeQuerySQL(java.
	 * lang.Class, java.lang.String, java.lang.Object[])
	 */
	@Override
	public <T extends BaseEntity> List<T> executeQuerySQL(Class<T> entityClass, String sql, Object[] params) throws SQLException {
		return this.executeQuerySQL(entityClass, sql, params, -1, -1);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.mvity.framework.common.dao.impl.BaseJdbcDao#executeQueryCountSQL(
	 * java.lang.String, java.lang.Object[])
	 */
	@Override
	public int executeQueryCountSQL(String sql, Object[] params) throws SQLException {
		logger.info(sql + ArrayUtils.toString(params));
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet resultSet = null;
		try {
			conn = this.getConnection();
			sql = sql.toUpperCase();
			sql = "SELECT COUNT(ID) FROM" + sql.split(" FROM ")[1];
			pstat = conn.prepareStatement(sql);
			if (params != null) {
				for (int i = 1; i <= params.length; i++) {
					pstat.setObject(i, params[i]);
				}
			}
			resultSet = pstat.executeQuery();
			if (resultSet.next()) {
				return resultSet.getInt(1);
			}
			return 0;
		} finally {
			if (resultSet != null) {
				resultSet.close();
			}
			if (pstat != null) {
				pstat.close();
			}
			this.releaseConnection(conn);
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.mvity.framework.common.dao.impl.BaseJdbcDao#executeQuerySQL(java.
	 * lang.Class, java.lang.String, java.lang.Object[], int, int)
	 */
	@Override
	public <T extends BaseEntity> List<T> executeQuerySQL(Class<T> entityClass, String sql, Object[] params, int pageSize, int pageNum)
	        throws SQLException {
		logger.info(sql + ArrayUtils.toString(params));
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet resultSet = null;
		try {
			conn = this.getConnection();
			pstat = conn.prepareStatement(sql);
			if (params != null) {
				for (int i = 1; i <= params.length; i++) {
					pstat.setObject(i, params[i]);
				}
			}
			if (pageSize != -1 && pageNum != -1) {
				pstat.setMaxRows(pageSize);
				pstat.setFetchSize(pageSize * pageNum + 1);
			}
			resultSet = pstat.executeQuery();
			return this.buildEntity(entityClass, resultSet);
		} finally {
			if (resultSet != null) {
				resultSet.close();
			}
			if (pstat != null) {
				pstat.close();
			}
			this.releaseConnection(conn);
		}
	}

	/**
	 * 将结果集绑定到实体对象
	 * 
	 * @param clazz
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	protected <T extends BaseEntity> List<T> buildEntity(Class<T> entityClass, ResultSet rs) throws SQLException {
		if (rs == null) {
			return null;
		}
		List<T> list = new ArrayList<T>();
		T object = null;
		List<String> columnNames = new ArrayList<String>();
		try {
			ResultSetMetaData md = rs.getMetaData();
			for (int i = 1; i <= md.getColumnCount(); i++) {
				columnNames.add(md.getColumnName(i));
			}
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		if (rs.next()) {
			try {
				object = entityClass.getConstructor(new Class[] {}).newInstance(new Object[] {});
				Field[] fields1 = entityClass.getSuperclass().getDeclaredFields();
				Field[] fields2 = entityClass.getDeclaredFields();
				Field[] fields = new Field[fields1.length + fields2.length];
				String firstLetter = "";
				Method setMethod = null;
				String setMethodName = "";
				for (int i = 0; i < fields.length; i++) {
					// 获取属性名称
					String fieldName = fields[i].getName();
					if (fieldName.equals("serialVersionUID")) {
						continue;
					}
					if (!columnNames.contains(fieldName)) {
						continue;
					}
					Column c = fields[i].getAnnotation(Column.class);
					firstLetter = fieldName.substring(0, 1).toUpperCase();
					setMethodName = "set" + firstLetter + fieldName.substring(1);
					if (setMethodName.startsWith("setIs")) {
						setMethod = entityClass.getMethod(setMethodName.replace("setIs", "set"), new Class[] { Boolean.class });
					} else {
						setMethod = entityClass.getMethod(setMethodName, new Class[] { fields[i].getType() });
					}
					setMethod.invoke(object, new Object[] { rs.getObject(c.name().toLowerCase()) });
				}
			} catch (Exception e) {
				logger.error(e.getMessage());
			}
			list.add(object);
		}
		return list;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.mvity.framework.common.dao.impl.BaseJdbcDao#queryForList(java.lang
	 * .Class, java.lang.String, java.lang.Object[], int, int)
	 */
	@Override
	public <T extends BaseEntity> List<T> queryForList(Class<T> entityClass, String sql, Object[] params, int start, int end) {
		if (start != -1 && end != -1) {
			sql = this.getPagesSQL(sql, start, end);
		}
		return jdbcTemplate.queryForList(sql, entityClass, params);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.mvity.framework.common.dao.impl.BaseJdbcDao#queryForCount(java.lang
	 * .String, java.lang.Object[])
	 */
	@Override
	public int queryForCount(String sql, Object[] params) {
		sql = "SELECT COUNT(1) FROM " + sql.toLowerCase().split(" from ")[1];
		return jdbcTemplate.queryForInt(sql, params);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.mvity.framework.common.dao.impl.BaseJdbcDao#getPagesSQL(java.lang
	 * .String, int, int)
	 */
	@Override
	public String getPagesSQL(String sql, int start, int end) {
		if (this.driverClass.toLowerCase().contains("oracle")) {

		} else if (this.driverClass.toLowerCase().contains("mysql")) {
			return sql + "LIMIT " + start + "," + end + " ";
		} else if (this.driverClass.toLowerCase().contains("sqlserver")) {

		} else if (this.driverClass.toLowerCase().contains("postgresql")) {

		}
		return sql;
	}
}
